﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using ModelLayer;

namespace DbLayer
{
    public class AllergiesDb
    {
        private SqlUtility sqlutility;
        private SqlConnection con;


        public AllergiesDb()
        {
            sqlutility = new SqlUtility();
        }

        //Insert Allergies into database
        public int InsertAllergies(Allergies _allergies)
        {
            const string query = "Insert into Allergies(name, summary) values(@name, @summary)";
            using (SqlConnection con = sqlutility.GetCon())
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", _allergies.Name);
                    cmd.Parameters.AddWithValue("@summary", _allergies.Summary);
                    return sqlutility.ExecuteData(cmd, con);
                }
            }
        }

        //Build Allergies Obj
        public Allergies BuildAllergies(SqlDataReader _rdr)
        {
            Allergies temAllergies = new Allergies();

            temAllergies.Id = sqlutility.GetSafeInt(_rdr, 0);
            temAllergies.Name = sqlutility.GetSafeString(_rdr, 1);
            temAllergies.Summary = sqlutility.GetSafeString(_rdr, 2);

            return temAllergies;
        }

        //return a Allergies by name
        public Allergies GetAllergies(string _name)
        {
            Allergies temAllergies = null;

            using (con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "Select * from Allergies where name=@name";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", _name);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            temAllergies = BuildAllergies(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return temAllergies;
        }
        public Allergies GetAllergiesById(int _id)
        {
            Allergies temAllergies = new Allergies();
            using (con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "Select * from Allergies where id=@id";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@id", _id);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            temAllergies = BuildAllergies(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return temAllergies;
        }

        //return a List<> of all Allergies in Db
        public List<Allergies> SelectAllAllergies()
        {
            List<Allergies> allergiesList = new List<Allergies>();
            using (con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "Select * from Allergies ORDER BY Name ASC";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            Allergies temAllergies = new Allergies();
                            temAllergies = BuildAllergies(rdr);
                            allergiesList.Add(temAllergies);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return allergiesList;
        }
        public List<Allergies>GetAllegiesFromProduct(int _productId)
        {
            List<int> tList = GetAllergiesIdFromProductId(_productId);
            return GetAllergiesFromProduct(tList);
        }
        private List<int> GetAllergiesIdFromProductId(int _productId)
        {
            List<int> allergiesInt = new List<int>();
            using (con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "Select * from ProductAllergies where productId=@productId";
                
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@productId", _productId);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            int temInt = new int();
                            temInt = (int)rdr["allergiesId"];
                            allergiesInt.Add(temInt);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return allergiesInt;
        }
        private List<Allergies> GetAllergiesFromProduct(List<int> _list)
        {
            List<Allergies> retList = new List<Allergies>();
            foreach(int i in _list)
            {
                Allergies temAllergi = new Allergies();
                temAllergi = GetAllergiesById(i);
                retList.Add(temAllergi);
            }
            return retList;
        }
    }
}
